using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Metro.Admin.BusinessEntities;
using Microsoft.Practices.EnterpriseLibrary.Data;   

namespace Metro.Admin.DataAccess
{
    partial class AdminDataSource
    {

        public List<User> GetUsers()
        {
            List<User> users = null;
            string id = string.Empty;
            string login = string.Empty;
            string idCustomer = string.Empty;
            int type = 0;
            DateTime passwordExpiration;
            string surename = string.Empty;
            string name = string.Empty;
            string state = string.Empty;
            string salt = string.Empty;
            string hash = string.Empty;
            DateTime lastLoginDate;
            int failedPasswordAttemptCount = 0;
            string email = string.Empty;
            string passwordQuestion = string.Empty;
            string passwordAnswer = string.Empty;
            UserType userTypeValue= UserType.Adm;
            object objUserType = null;
            Customer customerUser = null;
            User userList = null;

            try
            {
                users = new List<User>();
                string sqlCommand = "SELECT [id],[login],[idCustomer],[type],[passwordExpiration],[surename],[name]" +
                                    ",[state],[salt],[hash],[lastLoginDate],[failedPasswordAttemptCount],[email]" +
                                    ",[passwordQuestion],[passwordAnswer] FROM [User] WHERE [state] = 'A'";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
                {
                    while (dataReader.Read())
                    {
                        id = dataReader["id"].ToString();
                        login = dataReader["login"].ToString();
                        idCustomer = dataReader["idCustomer"].ToString();
                        type = Convert.ToInt32(dataReader["type"]);
                        passwordExpiration = Convert.ToDateTime(dataReader["passwordExpiration"]);
                        surename = dataReader["surename"].ToString();
                        name = dataReader["name"].ToString();
                        state = dataReader["state"].ToString();
                        salt = dataReader["salt"].ToString();
                        hash = dataReader["hash"].ToString();
                        lastLoginDate = Convert.ToDateTime(dataReader["lastLoginDate"]);
                        failedPasswordAttemptCount = Convert.ToInt32(dataReader["failedPasswordAttemptCount"]);
                        email = dataReader["email"].ToString();
                        passwordQuestion = dataReader["passwordQuestion"].ToString();
                        passwordAnswer = dataReader["passwordAnswer"].ToString();

                        customerUser = this.GetCustomer(idCustomer); 
                        objUserType = Enum.Parse(typeof(UserType), type.ToString());
                        userTypeValue = (UserType)objUserType;

                        userList = new User(id, login, idCustomer, userTypeValue, passwordExpiration, surename, name, CustomerServices.GetState(state), salt, hash, lastLoginDate, failedPasswordAttemptCount, email, passwordQuestion, passwordAnswer);
                        userList.CustomerName = customerUser.Name + " " + customerUser.SureName;  
                        users.Add(userList);
                    }
                }
            }
            catch(Exception ex)
            {
                throw ex;            
            }

            return users;
        }
        public void UpdateUser(User user)
        {
            Guid idUser= new Guid(user.Id);
            Guid idCustomer = new Guid(user.CustomerId);
            try
            {
                string sqlCommand = "UPDATE [User] SET [login] = @login,[idCustomer] = @idCustomer,[type] = @type," +
                                    "[passwordExpiration] = @passwordExpiration,[surename] = @surename," + 
                                    "[name] = @name,[state] = @state,[salt] = @salt,[hash] = @hash," + 
                                    "[email] = @email,[passwordQuestion] = @passwordQuestion ,[passwordAnswer] = @passwordAnswer WHERE [id] = @id";
                                    
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                metroDb.AddInParameter(dbCommand, "@id", DbType.Guid, idUser);
                metroDb.AddInParameter(dbCommand, "@login", DbType.String, user.Login);
                metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                metroDb.AddInParameter(dbCommand, "@type", DbType.Int32, (int)user.UserTypeProp);
                metroDb.AddInParameter(dbCommand, "@passwordExpiration", DbType.DateTime, user.DatePasswordExpiration);
                metroDb.AddInParameter(dbCommand, "@surename", DbType.String, user.LastName);
                metroDb.AddInParameter(dbCommand, "@name", DbType.String, user.Name);
                metroDb.AddInParameter(dbCommand, "@state", DbType.String, CustomerServices.State2String(user.State));
                metroDb.AddInParameter(dbCommand, "@salt", DbType.String, user.Salt);
                metroDb.AddInParameter(dbCommand, "@hash", DbType.String, user.Hash);
                metroDb.AddInParameter(dbCommand, "@email", DbType.String, user.Email);
                metroDb.AddInParameter(dbCommand, "@passwordQuestion", DbType.String, user.PasswordQuestion);
                metroDb.AddInParameter(dbCommand, "@passwordAnswer", DbType.String, user.PasswordAnswer);

                metroDb.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void CreateUser(User user)
        {
            Guid idCustomer = new Guid(user.CustomerId);
            try
            {
                string sqlCommand = "INSERT INTO [User]([login],[idCustomer],[type],[passwordExpiration],[surename],"+
                                    "[name],[state],[salt],[hash],[lastLoginDate],[failedPasswordAttemptCount]," +
                                    "[email],[passwordQuestion],[passwordAnswer]) VALUES (@login,@idCustomer,@type,"+
                                    "@passwordExpiration,@surename,@name,@state,@salt,@hash,@lastLoginDate,0," +
                                    "@email,@passwordQuestion,@passwordAnswer)";

                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                metroDb.AddInParameter(dbCommand, "@login", DbType.String, user.Login);
                metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                metroDb.AddInParameter(dbCommand, "@type", DbType.Int32, (int)user.UserTypeProp);
                metroDb.AddInParameter(dbCommand, "@passwordExpiration", DbType.DateTime, user.DatePasswordExpiration);
                metroDb.AddInParameter(dbCommand, "@surename", DbType.String, user.LastName);
                metroDb.AddInParameter(dbCommand, "@name", DbType.String, user.Name);
                metroDb.AddInParameter(dbCommand, "@state", DbType.String,CustomerServices.State2String(user.State));
                metroDb.AddInParameter(dbCommand, "@salt", DbType.String, user.Salt);
                metroDb.AddInParameter(dbCommand, "@hash", DbType.String, user.Hash);
                metroDb.AddInParameter(dbCommand, "@lastLoginDate", DbType.DateTime, DateTime.Now.AddYears(-1));
                metroDb.AddInParameter(dbCommand, "@email", DbType.String, user.Email);
                metroDb.AddInParameter(dbCommand, "@passwordQuestion", DbType.String, user.PasswordQuestion);
                metroDb.AddInParameter(dbCommand, "@passwordAnswer", DbType.String, user.PasswordAnswer);
                metroDb.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void DeleteUser(User user)
        {
            Guid idUser = new Guid(user.Id);
            try
            {
                string sqlCommand = "UPDATE [User] SET [state] = 'I' WHERE [id] = @id";
                                    
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                metroDb.AddInParameter(dbCommand, "@id", DbType.Guid, idUser);
                metroDb.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public User GetUser(string idUser)
        {
            Guid id = new Guid(idUser);
            string login = string.Empty;
            string userId = string.Empty;
            User retUser=null;
            string idCustomer = string.Empty;
            int type = 0;
            DateTime passwordExpiration;
            string surename = string.Empty;
            string name = string.Empty;
            string state = string.Empty;
            string salt = string.Empty;
            string hash = string.Empty;
            DateTime lastLoginDate;
            int failedPasswordAttemptCount = 0;
            string email = string.Empty;
            string passwordQuestion = string.Empty;
            string passwordAnswer = string.Empty;
            UserType userTypeValue= UserType.Adm;
            object objUserType = null;

            try
            {
                string sqlCommand = "SELECT [id],[login],[idCustomer],[type],[passwordExpiration],[surename],[name]" +
                                    ",[state],[salt],[hash],[lastLoginDate],[failedPasswordAttemptCount],[email]" +
                                    ",[passwordQuestion],[passwordAnswer] FROM [User] WHERE [id] = @id"; 
                
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@id", DbType.Guid, id);
               
                using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
                {
                    while (dataReader.Read())
                    {
                        userId = dataReader["id"].ToString();
                        login = dataReader["login"].ToString();
                        idCustomer = dataReader["idCustomer"].ToString();
                        type = Convert.ToInt32(dataReader["type"]);
                        passwordExpiration = Convert.ToDateTime(dataReader["passwordExpiration"]);
                        surename = dataReader["surename"].ToString();
                        name = dataReader["name"].ToString();
                        state = dataReader["state"].ToString();
                        salt = dataReader["salt"].ToString();
                        hash = dataReader["hash"].ToString();
                        lastLoginDate = Convert.ToDateTime(dataReader["lastLoginDate"]);
                        failedPasswordAttemptCount = Convert.ToInt32(dataReader["failedPasswordAttemptCount"]);
                        email = dataReader["email"].ToString();
                        passwordQuestion = dataReader["passwordQuestion"].ToString();
                        passwordAnswer = dataReader["passwordAnswer"].ToString();

                        objUserType = Enum.Parse(typeof(UserType), type.ToString());
                        userTypeValue = (UserType)objUserType;

                        retUser = new User(userId,login,idCustomer, userTypeValue, passwordExpiration, surename, name, CustomerServices.GetState(state), salt, hash, lastLoginDate, failedPasswordAttemptCount, email, passwordQuestion, passwordAnswer);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return retUser; 
        }
        public User GetUserByLogin(string login)
        {
            string userId = string.Empty;
            User retUser = null;
            string idCustomer = string.Empty;
            int type = 0;
            DateTime passwordExpiration;
            string surename = string.Empty;
            string name = string.Empty;
            string state = string.Empty;
            string salt = string.Empty;
            string hash = string.Empty;
            DateTime lastLoginDate;
            int failedPasswordAttemptCount = 0;
            string email = string.Empty;
            string passwordQuestion = string.Empty;
            string passwordAnswer = string.Empty;
            UserType userTypeValue = UserType.Adm;
            object objUserType = null;
            Customer customerUser = null;

            try
            {
                string sqlCommand = "SELECT [id],[login],[idCustomer],[type],[passwordExpiration],[surename],[name]" +
                                    ",[state],[salt],[hash],[lastLoginDate],[failedPasswordAttemptCount],[email]" +
                                    ",[passwordQuestion],[passwordAnswer] FROM [User] WHERE [login] = @login";

                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@login", DbType.String, login);

                using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
                {
                    while (dataReader.Read())
                    {
                        userId = dataReader["id"].ToString();
                        login = dataReader["login"].ToString();
                        idCustomer = dataReader["idCustomer"].ToString();
                        type = Convert.ToInt32(dataReader["type"]);
                        passwordExpiration = Convert.ToDateTime(dataReader["passwordExpiration"]);
                        surename = dataReader["surename"].ToString();
                        name = dataReader["name"].ToString();
                        state = dataReader["state"].ToString();
                        salt = dataReader["salt"].ToString();
                        hash = dataReader["hash"].ToString();
                        lastLoginDate = Convert.ToDateTime(dataReader["lastLoginDate"]);
                        failedPasswordAttemptCount = Convert.ToInt32(dataReader["failedPasswordAttemptCount"]);
                        email = dataReader["email"].ToString();
                        passwordQuestion = dataReader["passwordQuestion"].ToString();
                        passwordAnswer = dataReader["passwordAnswer"].ToString();

                        objUserType = Enum.Parse(typeof(UserType), type.ToString());
                        userTypeValue = (UserType)objUserType;

                        retUser = new User(userId, login, idCustomer, userTypeValue, passwordExpiration, surename, name, CustomerServices.GetState(state), salt, hash, lastLoginDate, failedPasswordAttemptCount, email, passwordQuestion, passwordAnswer);

                        customerUser = this.GetCustomer(idCustomer);
                        retUser.CustomerName = customerUser.Name + " " + customerUser.SureName;
                        retUser.CustomerCommision = customerUser.Commission;
                        retUser.TechnicalNameCustomer = customerUser.TechnicalName;
                        retUser.Position = customerUser.Position;
                        retUser.Profit = customerUser.Profit;
                        retUser.TransitBuyCredit = customerUser.TransitBuyCredit;
                        retUser.TransitSellCredit = customerUser.TransitSellCredit;
                        retUser.SellCredit = customerUser.SellCredit;
                        retUser.BuyCredit = customerUser.BuyCredit;
                        retUser.AvgBuy = customerUser.AvgBuy;
                        retUser.AvgSell = customerUser.AvgSell;
                        retUser.CustomerType = customerUser.CustomerType;    
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return retUser;
        }    
    }
}
